﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using Oracle.DataAccess.Client;
using System.Data.SqlClient;

namespace wfaFoxMigrator
{
    public class OperationsOnItem
    {       
        public SortedList<string,dynamic> Values { get; set; }
        public SortedList<string,string> Operations { get; set; }
        public SortedList<string,string> FieldsAndTypes { get; set; }

        public OperationsOnItem()
        {
            if (FieldsAndTypes == null)
            {
                FieldsAndTypes = new SortedList<string, string>();
            }
            if (Operations == null)
            {
                Operations = new SortedList<string, string>();
            }
            if (Values == null)
            {
                Values = new SortedList<string, dynamic>();
            }
        }

        private string GenerateOracleTableSchema(string TableName)
        {
            string Schema = string.Empty;
            string columns = string.Empty;
            foreach (KeyValuePair<string, string> kvp in FieldsAndTypes)
            {
                columns += kvp.Key + " " + kvp.Value + ", "; // +Environment.NewLine;
            }
            if (columns.Length > 1) { columns = columns.Remove(columns.LastIndexOf(", "), 1); }
            Schema = String.Format(Properties.Settings.Default.TableSchemmaOracle, Properties.Settings.Default.InitialCatalog, TableName, columns);
            return Schema;
        }
        private string GenerateSqlTableSchema(string TableName)
        {           
            string Schema = string.Empty;
            string columns = string.Empty;
            foreach (KeyValuePair<string, string> kvp in FieldsAndTypes)
            {
                columns += kvp.Key + " " + kvp.Value + ", "; //+ Environment.NewLine;
            }
            columns = columns.Remove(columns.LastIndexOf(", "), 1);
            Schema = String.Format(Properties.Settings.Default.TableSchemmaOracle, Properties.Settings.Default.InitialCatalog, TableName, columns);
            return Schema;
        }
        public string GenerateTableSchema(string TableName)
        {
            string tableSchm = string.Empty;
            switch (Properties.Settings.Default.CurrentDB)
            {
                case "Oracle":
                    tableSchm = GenerateOracleTableSchema(TableName);
                    break;
                case "SqlServer":
                    tableSchm = GenerateSqlTableSchema(TableName);
                    break;
            }
            return tableSchm;
        }
        public string TranslateToInsertOperationToSql(string TableName, DataRow dr, out List<SqlParameter> prms)
        {
            string tbs = string.Empty;
            string date = string.Empty;
            switch (Properties.Settings.Default.CurrentDB)
            {
                case "Oracle":
                    tbs = Properties.Settings.Default.InsertClauseOracle;
                    date = Properties.Settings.Default.DateInOracle;
                    break;
                case "SqlServer":
                    tbs = Properties.Settings.Default.InsertClauseSql;
                    date = Properties.Settings.Default.DateInSql;
                    break;
            }
            string values = string.Empty;
            prms = new List<SqlParameter>();
            string columns = string.Empty;
            foreach (DataColumn dc in dr.Table.Columns)
            {
                if (this.FieldsAndTypes.Any(k => k.Key.Equals(dc.ColumnName) && k.Value.ToLower().Contains("string")))
                {                   
                    prms.Add(new SqlParameter(dc.ColumnName, dr[dc.ColumnName].ToString().Trim()));                    
                }
                else
                {
                    prms.Add(new SqlParameter(dc.ColumnName, dr[dc.ColumnName]));
                }
                values += "?, ";                
                columns +=  dc.ColumnName + ", ";
                #region Commented Code
                /*columns += "[" + kvp.Key + "], ";
                //if (this.FieldsAndTypes.Any(k => k.Key.Equals(dc.ColumnName) && k.Value.Name.ToLower().Equals("string")))
                //{
                //    values += "'" + dr[dc.ColumnName].ToString().Trim().Replace("'"," ") + "', ";
                //}
                //else if (this.FieldsAndTypes.Any(k => k.Key.Equals(dc.ColumnName) && k.Value.Name.ToLower().Equals("boolean")))
                //{
                //    values += (Convert.ToBoolean(dr[dc.ColumnName])) ? "'S'" : "'N'" + ", ";
                //}
                //else if (this.FieldsAndTypes.Any(k => k.Key.Equals(dc.ColumnName) && k.Value.Name.ToLower().Equals("datetime")))
                //{
                //    values += String.Format(date, dr[dc.ColumnName].ToString().Trim().Replace("'", " "), System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern);
                //}
                //else
                //{
                //    values += dr[dc.ColumnName] + ", ";
                }*/
                #endregion
            }
            if (!String.IsNullOrEmpty(columns) && !String.IsNullOrEmpty(values))
            {
                columns = columns.Remove(columns.LastIndexOf(", "), 1);
                values = values.Remove(values.LastIndexOf(", "), 1);
                tbs = String.Format(tbs, TableName, columns, values);
            }            
            return tbs;
        }
        public string TranslateToInsertOperationToOracle(string TableName, DataRow dr, out List<OracleParameter> prms)
        {
            string tbs = string.Empty;
            string date = string.Empty;
            switch (Properties.Settings.Default.CurrentDB)
            {
                case "Oracle":
                    tbs = Properties.Settings.Default.InsertClauseOracle;
                    date = Properties.Settings.Default.DateInOracle;
                    break;
                case "SqlServer":
                    tbs = Properties.Settings.Default.InsertClauseSql;
                    date = Properties.Settings.Default.DateInSql;
                    break;
            }
            string values = string.Empty;
            prms = new List<OracleParameter>();
            string columns = string.Empty;
            foreach (DataColumn dc in dr.Table.Columns)
            {
                if (this.FieldsAndTypes.Any(k => k.Key.Equals(dc.ColumnName) && k.Value.ToLower().Contains("string")))
                {
                    if (dr[dc.ColumnName].ToString().StartsWith("to_clob('"))
                    {
                        string val = dr[dc.ColumnName].ToString().Replace("to_clob", string.Empty).Replace("')", string.Empty);
                        prms.Add(new OracleParameter(dc.ColumnName,OracleDbType.Clob,val,ParameterDirection.Input));
                    }
                    else
                    {
                        prms.Add(new OracleParameter(dc.ColumnName, dr[dc.ColumnName].ToString().Trim()));
                    }
                }
                else
                {
                    Boolean result = default(Boolean);
                    if (Boolean.TryParse(dr[dc.ColumnName].ToString(), out result))
                    {
                        prms.Add(new OracleParameter(dc.ColumnName,Convert.ToInt16(result)));
                    }
                    else
                    {
                        prms.Add(new OracleParameter(dc.ColumnName, dr[dc.ColumnName]));
                    }
                }
                values += ":" + dc.ColumnName + ", ";
                columns += dc.ColumnName + ", ";               
            }
            if (!String.IsNullOrEmpty(columns) && !String.IsNullOrEmpty(values))
            {
                columns = columns.Remove(columns.LastIndexOf(", "), 1);
                values = values.Remove(values.LastIndexOf(", "), 1);
                tbs = String.Format(tbs, TableName, columns, values);
            }
            return tbs;
        }       
    }
}
